package com.hibernate.hql;

import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry ;

import org.hibernate.Query;
import org.hibernate.classic.Session;
import org.junit.Test;

import com.hibernate.domain.Classes;
import com.hibernate.domain.Student;
import com.hibernate.utils.HibernateUtil;

/**
 * hibernate之hql单表查询测试类
 * @author LC
 *
 */
public class SingleTableQueryTest extends HibernateUtil {
	
	/**
	 * 查询所有的数据,常用
	 */
	@Test
	public void queryAllData() {
		Session session = sessionFactory.openSession() ;
		List<Classes> list = session.createQuery("from Classes").list() ;
		for (Classes classes : list) {
			System.out.println(classes);
		}
		session.close() ;
	}
	
	/**
	 * 查询classes表中的一些属性——只能以内存结构类型接收返回结果，结构不好，不方便操作
	 */
	@Test
	public void querySomeProperties_by_memory_struct() {
		Session session = sessionFactory.openSession() ;
		List<Object[]> list = session.createQuery("select cname, description from Classes").list() ;
		for (Object[] objects : list) {
			System.out.println(objects[0]);
			System.out.println(objects[1]);
		}
		session.close() ;
	}
	
	/**
	 * 带构造器的属性查询，内存结构面向对象，方便操作,常用
	 */
	@Test
	public void querySomeProperties_by_constructor() {
		Session session = sessionFactory.openSession() ;
		String hql = "select new com.hibernate.domain.Classes(c.cname, c.description) from Classes c" ;
		List<Classes> list = session.createQuery(hql).list() ;
		for (Classes classes : list) {
			System.out.println(classes.getCname() + "," + classes.getDescription());
		}
		session.close() ;
	}
	
	/**
	 * 传递参数的查询1——通过角标动态注入参数,常用
	 */
	@Test
	public void querySomeProperties_by_Constructor_and_parameters_1() {
		Session session = sessionFactory.openSession() ;
		String hql = "select new com.hibernate.domain.Classes(c.cname, c.description) " +
												"from Classes c where c.cname=? and c.description=?" ;
		Query query = session.createQuery(hql) ;
		query.setParameter(0, "1班") ;
		query.setParameter(1, "培训1班") ;
		List<Classes> list = query.list() ;
		for (Classes classes : list) {
			System.out.println(classes.getCname() + "," + classes.getDescription());
		}
		session.close() ;
	}
	
	/**
	 * 传递参数的查询2——通过属性名称动态注入参数,常用
	 */
	@Test
	public void querySomeProperties_by_Constructor_and_parameters_2() {
		Session session = sessionFactory.openSession() ;
		String hql = "select new com.hibernate.domain.Classes(c.cname, c.description) " +
												"from Classes c where c.cname=:cname and c.description=:description" ;
		Query query = session.createQuery(hql) ;
		query.setString("cname", "1班") ;
		query.setString("description", "培训1班") ;
		List<Classes> list = query.list() ;
		for (Classes classes : list) {
			System.out.println(classes.getCname() + "," + classes.getDescription());
		}
		session.close() ;
	}
	
	/**
	 *动态拼接查询条件，理解
	 */
	public void queryByDynamicParameters(Map<String, Object> variables) {
		Session session = sessionFactory.openSession() ;
		StringBuffer stringBuffer = new StringBuffer() ;
		stringBuffer.append("from Classes ") ;
		stringBuffer.append("where 1=1") ;
		for (Entry<String, Object> entry : variables.entrySet()) {
			stringBuffer.append(" and " + entry.getKey() + "=:" + entry.getKey()) ;
		}
		Query query = session.createQuery(stringBuffer.toString()) ;
		for(Entry<String, Object> entry : variables.entrySet()) {
			query.setParameter(entry.getKey(), entry.getValue()) ;
		} 
		List<Classes> list = query.list() ;
		for (Classes classes : list) {
			System.out.println(classes.getCname() + "," + classes.getDescription());
		}
		session.close() ;
	}
	
	/**
	 * 客户端
	 */
	@Test
	public void test() {
		Map<String, Object> variables = new HashMap<String, Object>() ;
		variables.put("cname", "1班") ;
		variables.put("description", "培训1班") ;
		this.queryByDynamicParameters(variables) ;
	}
	
	/**
	 * 计算一张表的行数,常用
	 *   count函数默认的是Long类型
	 */
	@Test
	public void testQueryCount() {
		Session session = sessionFactory.openSession() ;
		String hql = "select count(c.cid) from Classes c" ;
		Long count = (Long) session.createQuery(hql).uniqueResult() ;
		System.out.println(count);
		session.close() ;
	}
	
	/**
	 * 查询所有的班级的所有学生,常用
	 */
	@Test
	public void testSubSelect() {
		Session session = sessionFactory.openSession() ;
		String hql = "from Student s where s.classes.cid in (select cid from Classes )" ;
		List<Student> list = session.createQuery(hql).list() ;
		for (Student student : list) {
			System.out.println(student.getSname() + "," + student.getDescription());
		}
	}
	
}
